﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SassModule
{
    public static class ExcelHelper
    {
        #region 私有方法
        //获取cell的数据，并设置为对应的数据类型
        public static object GetCellValue(ICell cell)
        {
            object value = null;
            try
            {
                if (cell.CellType != CellType.Blank)
                {
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            // Date comes here
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                value = cell.DateCellValue;
                            }
                            else
                            {
                                // Numeric type
                                value = cell.NumericCellValue;
                            }
                            break;
                        case CellType.Boolean:
                            // Boolean type
                            value = cell.BooleanCellValue;
                            break;
                        case CellType.Formula:
                            value = cell.CellFormula;
                            break;
                        default:
                            // String type
                            value = cell.StringCellValue;
                            break;
                    }
                }
            }
            catch (Exception)
            {
                value = "";
            }

            return value;
        }
        public static void SetCellValue(ICell cell, object obj)
        {
            if (obj == null) return;
            if (obj.GetType() == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj.GetType() == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj.GetType() == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj.GetType() == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType() == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj.GetType() == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }

        /// <summary>
        /// 判断是否为兼容模式
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool GetIsCompatible(string filePath)
        {
            return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
        }



        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(bool isCompatible)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook();
            }
            else
            {
                return new XSSFWorkbook();
            }
        }

        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }

        /// <summary>
        /// 创建表格头单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        public static ICellStyle GetHeaderCellStyle(IWorkbook workbook, int level = 1)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            IFont font1 = workbook.CreateFont();//字体
            if (level > 1)
            {
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
                style.BorderTop = BorderStyle.None;
                style.BorderBottom = BorderStyle.None;
                style.BorderLeft = BorderStyle.None;
                style.BorderRight = BorderStyle.None;
                font1.FontHeightInPoints = 10;
            }
            else
            {
                style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
                style.BorderTop = BorderStyle.Thin;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                font1.FontHeightInPoints = 12;
            }
            style.FillPattern = FillPattern.SolidForeground;
            //style.FillPattern = FillPattern.SolidForeground;          
            font1.Boldweight = short.MaxValue;
            font1.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
            //font1.FontName = "宋体";
            style.SetFont(font1);
            return style;
        }

        public static ICellStyle GetDataCellStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //style.FillPattern = FillPattern.SolidForeground;
            //style.FillForegroundColor =  NPOI.HSSF.Util.HSSFColor.Tan.Index;
            IFont font1 = workbook.CreateFont();//字体
            font1.FontHeightInPoints = 10;
            font1.Boldweight = short.MaxValue;
            font1.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
            //font1.FontName = "宋体";
            style.SetFont(font1);
            return style;
        }

        public static ICellStyle GetTitleStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            //style.FillPattern = FillPattern.SolidForeground;
            //style.FillForegroundColor =  NPOI.HSSF.Util.HSSFColor.Tan.Index;
            IFont font1 = workbook.CreateFont();//字体
            font1.FontHeightInPoints = 14;
            font1.Boldweight = short.MaxValue;
            font1.IsBold = true;
            font1.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
            //font1.FontName = "宋体";
            style.SetFont(font1);
            return style;
        }

        public static ICellStyle GetCellStyleOfReport(bool Isbold, string Fontname, bool IsClass, IWorkbook workbook)
        {
            ICellStyle dtlStyle = workbook.CreateCellStyle();
            IFont dtlFont = workbook.CreateFont();
            dtlFont.IsBold = Isbold;
            dtlFont.FontName = Fontname;
            dtlStyle.SetFont(dtlFont);
            if (!IsClass)
            {
                dtlStyle.BorderLeft = BorderStyle.Thin;
                dtlStyle.BorderRight = BorderStyle.Thin;
                dtlStyle.BorderTop = BorderStyle.Thin; ;
                dtlStyle.BorderBottom = BorderStyle.Thin;

            }

            return dtlStyle;
        }

        #region    ISheet单元格样式
        /// <summary>
                /// 返回单元格颜色样式
                /// </summary>
                /// <param name="color">传入的颜色HSSFColor.Blue.Index</param>
                /// <param name="workbook">ISheet</param>
                /// <returns></returns>
        private static ICellStyle CellColor(short color, IWorkbook workbook)
        {
            ICellStyle dtlStyle = workbook.CreateCellStyle();
            IFont dtlFont = workbook.CreateFont();
            dtlFont.Color = color;
            dtlStyle.SetFont(dtlFont);
            return dtlStyle;
        }
        /// <summary>
                /// 返回单元格数值型样式
                /// </summary>
                /// <param name="workbook">ISheet</param>
                /// <param name="format">数值样式0.0000</param>
                /// <returns></returns>
        private static ICellStyle CellNumericalFomat(IWorkbook workbook, string strFormat)
        {
            IDataFormat format = workbook.CreateDataFormat();
            ICellStyle cellPriceStyle = workbook.CreateCellStyle();
            cellPriceStyle.DataFormat = format.GetFormat(strFormat);
            return cellPriceStyle;
        }
        #endregion


        #endregion

        #region 公共导出方法
        /// <summary>
        /// 从工作表中生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowIndex);
            if (headerRow == null) return null;
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列，则不再继续向后读取
                    cellCount = i + 1;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            ICell cell = null;
            try
            {
                for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        cell = row.Cells[0];
                        if (row.Cells[0].CellType == CellType.Blank)
                        {
                            continue;
                        }
                        DataRow dataRow = table.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                var dataCell= row.GetCell(j);
                                if (dataCell.CellType==CellType.Formula)
                                {
                                    dataRow[j] = dataCell.NumericCellValue;

                                }
                                else
                                {
                                    dataRow[j] = dataCell.ToString();

                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }
            return table;
        }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataSet sourceDs, string filePath = null)
        {
            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetHeaderCellStyle(workbook);

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                DataTable table = sourceDs.Tables[i];
                string sheetName = "result" + i.ToString();
                ISheet sheet = workbook.CreateSheet(sheetName);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in table.Columns)
                {
                    ICell cell = headerRow.CreateCell(column.Ordinal);
                    cell.SetCellValue(column.ColumnName);
                    cell.CellStyle = cellStyle;
                }

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                    }

                    rowIndex++;
                }
            }

            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();
            workbook = null;

            return filePath;

        }


        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        public static string ExportToExcel(DataTable sourceTable, string sheetName = "result", string filePath = null)
        {
            if (sourceTable.Rows.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetHeaderCellStyle(workbook);

            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
            {
                ICell headerCell = headerRow.CreateCell(column.Ordinal);
                headerCell.SetCellValue(column.ColumnName);
                headerCell.CellStyle = cellStyle;
            }

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue((row[column] ?? "").ToString());
                }

                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;
            return filePath;
        }

        /// <summary>
        /// 由List导出Excel
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="data">在导出的List</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "result", string filePath = null) where T : class
        {
            if (data.Count <= 0) return null;

            if (string.IsNullOrEmpty(filePath)) return null;

            bool isCompatible = GetIsCompatible(filePath);

            IWorkbook workbook = CreateWorkbook(isCompatible);
            ICellStyle cellStyle = GetHeaderCellStyle(workbook);
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);

            for (int i = 0; i < headerNameList.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(headerNameList[i].Value);
                cell.CellStyle = cellStyle;
            }

            Type t = typeof(T);
            int rowIndex = 1;
            foreach (T item in data)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (int n = 0; n < headerNameList.Count; n++)
                {
                    object pValue = t.GetProperty(headerNameList[n].Key).GetValue(item, null);
                    dataRow.CreateCell(n).SetCellValue((pValue ?? "").ToString());
                }
                rowIndex++;
            }
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fs);
            fs.Dispose();

            sheet = null;
            headerRow = null;
            workbook = null;

            return filePath;
        }

        public static MemoryStream ToExcel<T>(List<T> datas, List<ExportFieldInfo> fieldInfies, int sheetRows = 65536)
        {
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //需要生成工作溥总簿
            int sheetCount = datas.Count / sheetRows + 1;
            int rowCount = datas.Count;
            for (int i = 0; i < sheetCount; i++)
            {
                //添加一个sheet
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet" + Convert.ToString(i));
                //给sheet添加第一行的头部标题
                NPOI.SS.UserModel.IRow rowTitle = sheet.CreateRow(0);
                for (int k = 0; k < fieldInfies.Count; k++)
                {
                    rowTitle.CreateCell(k).SetCellValue(fieldInfies.ElementAt(k).DisplayName);
                }
                //处理Excel一张工作簿只能放65536行记录的问题
                //因为头部占一行，所以要减1
                for (int j = 0; j < sheetRows - 1; j++)
                {
                    //将数据逐步写入sheet各个行
                    NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(j + 1);
                    int dataIndex = i * (sheetRows - 1) + j;
                    for (int k = 0; k < fieldInfies.Count; k++)
                    {
                        //获取类型
                        Type type = datas[dataIndex].GetType();
                        //获取指定名称的属性
                        System.Reflection.PropertyInfo propertyInfo = type.GetProperty(fieldInfies.ElementAt(k).FieldName);
                        //获取属性值
                        var value = propertyInfo.GetValue(datas[dataIndex], null);
                        switch (fieldInfies.ElementAt(k).DataType)
                        {
                            case DataTypeEnum.Int:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToInt32(value));
                                break;
                            case DataTypeEnum.Float:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));
                                break;
                            case DataTypeEnum.Double:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));
                                break;
                            case DataTypeEnum.String:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToString(value));
                                break;
                            case DataTypeEnum.DateTime:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.SSS"));
                                break;
                            case DataTypeEnum.Date:
                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));
                                break;
                            default:
                                break;
                        }
                    }
                    //所有记录循环完成
                    if (i * (sheetRows - 1) + (j + 1) == rowCount)
                    {
                        // 写入到客户端 
                        System.IO.MemoryStream ms = new System.IO.MemoryStream();
                        book.Write(ms);
                        ms.Seek(0, SeekOrigin.Begin);
                        return ms;
                    }
                }

            }
            return null;
        }
        /// <summary>
        /// 由DataGridView导出
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        //public static string ExportToExcel(DataGridView grid, string sheetName = "result", string filePath = null)
        //{
        //    if (grid.Rows.Count <= 0) return null;

        //    if (string.IsNullOrEmpty(filePath)) return null;

        //    bool isCompatible = GetIsCompatible(filePath);

        //    IWorkbook workbook = CreateWorkbook(isCompatible);
        //    ICellStyle cellStyle = GetCellStyle(workbook);
        //    ISheet sheet = workbook.CreateSheet(sheetName);

        //    IRow headerRow = sheet.CreateRow(0);

        //    for (int i = 0; i < grid.Columns.Count; i++)
        //    {
        //        ICell cell = headerRow.CreateCell(i);
        //        cell.SetCellValue(grid.Columns[i].Name);
        //        cell.CellStyle = cellStyle;
        //    }

        //    int rowIndex = 1;
        //    foreach (DataGridViewRow row in grid.Rows)
        //    {
        //        IRow dataRow = sheet.CreateRow(rowIndex);
        //        for (int n = 0; n < grid.Columns.Count; n++)
        //        {
        //            dataRow.CreateCell(n).SetCellValue((row.Cells[n].Value ?? "").ToString());
        //        }
        //        rowIndex++;
        //    }

        //    FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
        //    workbook.Write(fs);
        //    fs.Dispose();

        //    sheet = null;
        //    headerRow = null;
        //    workbook = null;

        //    return filePath;
        //}

        #endregion

        #region 公共导入方法

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        private static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }
            DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, sheetName, headerRowIndex, isCompatible);
            }
        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataSet</returns>
        private static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);

            //ISheet sheet = workbook.GetSheetAt(0);
            //DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
            //ds.Tables.Add(table);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }
            excelFileStream.Close();
            workbook = null;
            return ds;
        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = GetIsCompatible(excelFilePath);
                return ImportFromExcel(stream, headerRowIndex, isCompatible);
            }
        }

        #endregion

        #region 公共转换方法

        /// <summary>
        /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列名，如第0列为A，第1列为B...</returns>
        public static string ConvertColumnIndexToColumnName(int index)
        {
            index = index + 1;
            int system = 26;
            char[] digArray = new char[100];
            int i = 0;
            while (index > 0)
            {
                int mod = index % system;
                if (mod == 0) mod = system;
                digArray[i++] = (char)(mod - 1 + 'A');
                index = (index - 1) / 26;
            }
            StringBuilder sb = new StringBuilder(i);
            for (int j = i - 1; j >= 0; j--)
            {
                sb.Append(digArray[j]);
            }
            return sb.ToString();
        }


        /// <summary>
        /// 转化日期
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public static DateTime ConvertDate(object date)
        {
            string dtStr = (date ?? "").ToString();

            DateTime dt = new DateTime();

            if (DateTime.TryParse(dtStr, out dt))
            {
                return dt;
            }

            try
            {
                string spStr = "";
                if (dtStr.Contains("-"))
                {
                    spStr = "-";
                }
                else if (dtStr.Contains("/"))
                {
                    spStr = "/";
                }
                string[] time = dtStr.Split(spStr.ToCharArray());
                int year = Convert.ToInt32(time[2]);
                int month = Convert.ToInt32(time[0]);
                int day = Convert.ToInt32(time[1]);
                string years = Convert.ToString(year);
                string months = Convert.ToString(month);
                string days = Convert.ToString(day);
                if (months.Length == 4)
                {
                    dt = Convert.ToDateTime(date);
                }
                else
                {
                    string rq = "";
                    if (years.Length == 1)
                    {
                        years = "0" + years;
                    }
                    if (months.Length == 1)
                    {
                        months = "0" + months;
                    }
                    if (days.Length == 1)
                    {
                        days = "0" + days;
                    }
                    rq = "20" + years + "-" + months + "-" + days;
                    dt = Convert.ToDateTime(rq);
                }
            }
            catch
            {
                throw new Exception("日期格式不正确，转换日期失败！");
            }
            return dt;
        }

        /// <summary>
        /// 转化数字
        /// </summary>
        /// <param name="d">数字字符串</param>
        /// <returns></returns>
        public static decimal ConvertDecimal(object d)
        {
            string dStr = (d ?? "").ToString();
            decimal result = 0;
            if (decimal.TryParse(dStr, out result))
            {
                return result;
            }
            else
            {
                throw new Exception("数字格式不正确，转换数字失败！");
            }

        }

        #endregion
    }
    public enum DataTypeEnum
    {
        Int = 0,
        Float = 1,
        Double = 2,
        String = 3,
        DateTime = 4,
        Date = 5
    }
    public class ExportFieldInfo
    {
        /// <summary>
        /// 字段名，用于反射获取值
        /// </summary>
        public string FieldName { get; set; }
        /// <summary>
        /// 中文名，用于导出标题
        /// </summary>
        public string DisplayName { get; set; }
        /// <summary>
        /// 数据类型，用于强制转换，并进行格式化,其实利用反射也可以获取到数据类型，此处因为要处理Date和Date的显示格式
        /// </summary>
        public DataTypeEnum DataType { get; set; }
    }
}
